﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using ISS.IR.RRNMS.Library.BusinessLogic;

namespace ISS.IR.RRNMS.Library.DataAccess
{
    public static partial class Main
    {
        public static List<Item> SelectListOfAllAccounts()
        {
            try
            {
                adapter.SelectCommand.CommandText = "SelectListOfAllAccounts";
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Clear();

                DataTable dtMaster = new DataTable();
                adapter.Fill(dtMaster);

                List<Item> Accounts = new List<Item>();

                for (int i = 0; i < dtMaster.Rows.Count; i++)
                {
                    Item temp = new Item();
                    temp.GUID = Guid.Parse(Convert.ToString(dtMaster.Rows[i]["AccountId"]));
                    temp.Value = Convert.ToString(dtMaster.Rows[i]["FullName"]);
                    Accounts.Add(temp);

                }
                Item x = new Item();
                x.Id = 0;
                x.Value = "";
                Accounts.Insert(0, x);
                return Accounts;
            }
            catch (Exception e) { return null; }
        }
        public static List<Item> SelectListOfAllUsers()
        {
            try
            {
                adapter.SelectCommand.CommandText = "SelectListOfAllUsers";
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Clear();

                DataTable dtMaster = new DataTable();
                adapter.Fill(dtMaster);

                List<Item> Users = new List<Item>();

                for (int i = 0; i < dtMaster.Rows.Count; i++)
                {
                    Item temp = new Item();
                    temp.GUID = Guid.Parse(Convert.ToString(dtMaster.Rows[i]["UserId"]));
                    temp.Value = Convert.ToString(dtMaster.Rows[i]["FullName"]);
                    Users.Add(temp);

                }
                Item x = new Item();
                x.Id = 0;
                x.Value = "";
                Users.Insert(0, x);
                return Users;
            }
            catch (Exception e) { return null; }
        }
        public static List<Slot> SelectListOfAllSlots()
        {
            try
            {
                adapter.SelectCommand.CommandText = "SelectListOfAllSlots";
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Clear();

                DataTable dtMaster = new DataTable();
                adapter.Fill(dtMaster);

                List<Slot> Slots = new List<Slot>();


                for (int i = 0; i < dtMaster.Rows.Count; i++)
                {
                    Slot temp = new Slot();
                    temp.SlotId = Guid.Parse(Convert.ToString(dtMaster.Rows[i]["SlotId"]));
                    temp.FromHour = Convert.ToInt32(dtMaster.Rows[i]["FromHour"]);
                    temp.FromMinutes = Convert.ToInt32(dtMaster.Rows[i]["FromMinute"]);
                    temp.ToHours = Convert.ToInt32(dtMaster.Rows[i]["ToHour"]);
                    temp.ToMinutes = Convert.ToInt32(dtMaster.Rows[i]["ToMinute"]);
                    Slots.Add(temp);

                }

                return Slots;
            }
            catch (Exception e) { return null; }
        }
        public static List<Calendar> SelectCalendarEntitesByDateAndRoom(DateTime Date, int RoomId)
        {
            try
            {
                adapter.SelectCommand.CommandText = "SelectCalendarEntitesByDateAndRoom";
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Clear();
                adapter.SelectCommand.Parameters.Add("@Date", SqlDbType.Date).Value = Date;
                adapter.SelectCommand.Parameters.Add("@RoomId", SqlDbType.Int).Value = RoomId;

                DataTable dtMaster = new DataTable();
                adapter.Fill(dtMaster);

                List<Calendar> CalenderEntries = new List<Calendar>();

                for (int i = 0; i < dtMaster.Rows.Count; i++)
                {
                    CalenderEntries.Add(new Calendar());

                    #region Account
                    Item AccountTemp = new Item();
                    AccountTemp.GUID = Guid.Parse(Convert.ToString(dtMaster.Rows[i]["AccountId"]));
                    //   AccountTemp.Id = Convert.ToInt32(dtMaster.Rows[i]["AccountId"]);
                    AccountTemp.Value = Convert.ToString(dtMaster.Rows[i]["AccountsName"]);
                    CalenderEntries[i].Account = AccountTemp;
                    #endregion
                    #region User
                    Item UserTemp = new Item();
                    UserTemp.GUID = Guid.Parse(Convert.ToString(dtMaster.Rows[i]["UserId"]));
                    //  UserTemp.Id = Convert.ToInt32(dtMaster.Rows[i]["UserId"]);
                    UserTemp.Value = Convert.ToString(dtMaster.Rows[i]["UserName"]);
                    CalenderEntries[i].User = UserTemp;
                    #endregion
                    #region Room
                    Item RoomTemp = new Item();
                    RoomTemp.Id = Convert.ToInt32(dtMaster.Rows[i]["RoomId"]);
                    RoomTemp.Value = Convert.ToString(dtMaster.Rows[i]["RoomName"]);
                    CalenderEntries[i].Room = RoomTemp;
                    #endregion

                    #region Slot
                    Slot SlotTemp = new Slot();
                    SlotTemp.SlotId = Guid.Parse(Convert.ToString(dtMaster.Rows[i]["SlotId"]));
                    SlotTemp.FromHour = Convert.ToInt32(dtMaster.Rows[i]["FromHour"]);
                    SlotTemp.FromMinutes = Convert.ToInt32(dtMaster.Rows[i]["FromMinute"]);
                    SlotTemp.ToHours = Convert.ToInt32(dtMaster.Rows[i]["ToHour"]);
                    SlotTemp.ToMinutes = Convert.ToInt32(dtMaster.Rows[i]["ToMinute"]);
                    CalenderEntries[i].Slot = SlotTemp;
                    #endregion

                    CalenderEntries[i].Date = Convert.ToDateTime(dtMaster.Rows[i]["Date"]);
                }


                return CalenderEntries;
            }
            catch (Exception e) { return null; }
        }
        public static DataTable SelectItemsByCategory(int CategoryId)
        {
            try
            {
                adapter.SelectCommand.CommandText = "SelectItemsByCategory";
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Clear();
                adapter.SelectCommand.Parameters.Add("@CategoryId", SqlDbType.Int).Value = CategoryId;

                DataTable dtMaster = new DataTable();
                adapter.Fill(dtMaster);

                return dtMaster;
            }
            catch (Exception e) { return null; }
        }
        public static int InsertNewAppointmant(Calendar calender)
        {
            try
            {
                command.CommandText = "InsertNewAppointmant";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();
                command.Parameters.Add("@Date", SqlDbType.Date).Value = calender.Date;
                command.Parameters.Add("@RoomId", SqlDbType.Int).Value = calender.Room.Id;
                command.Parameters.Add("@SlotId", SqlDbType.UniqueIdentifier).Value = calender.Slot.SlotId;
                command.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = calender.User.GUID;
                command.Parameters.Add("@AccountId", SqlDbType.UniqueIdentifier).Value = calender.Account.GUID;

                OpenConnection();
                int Rowindex = command.ExecuteNonQuery();
                CloseConnection();
                return Rowindex;
            }
            catch (Exception e) { return 0; }
        }
        public static List<Guid> SelectSlotsToCheck(Calendar calender)
        {
            try
            {
                adapter.SelectCommand.CommandText = "SelectSlotsToCheck";
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Clear();
                adapter.SelectCommand.Parameters.Add("@Date", SqlDbType.Date).Value = calender.Date;
                adapter.SelectCommand.Parameters.Add("@RoomId", SqlDbType.Int).Value = calender.Room.Id;
                adapter.SelectCommand.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = calender.User.GUID;



                DataSet dsMaster = new DataSet();
                adapter.Fill(dsMaster);

                List<Guid> Slots = new List<Guid>();

                for (int i = 0; i < dsMaster.Tables[0].Rows.Count; i++)
                {
                    Slots.Add(new Guid());
                    Slots[i] = Guid.Parse(Convert.ToString(dsMaster.Tables[0].Rows[i]["SlotId"]));

                }
                for (int i = 0; i < dsMaster.Tables[1].Rows.Count; i++)
                {
                    Slots.Add(new Guid());
                    Slots[i] = Guid.Parse(Convert.ToString(dsMaster.Tables[1].Rows[i]["SlotId"]));

                }
                return Slots;
            }
            catch (Exception e)
            { return null; }

        }
        public static bool SelectSlotsToCheckBeforeInsert(Calendar calender)
        {
            try
            {
                adapter.SelectCommand.CommandText = "SelectSlotsToCheckBeforeInsert";
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Clear();
                adapter.SelectCommand.Parameters.Add("@Date", SqlDbType.Date).Value = calender.Date;
                adapter.SelectCommand.Parameters.Add("@RoomId", SqlDbType.Int).Value = calender.Room.Id;
                adapter.SelectCommand.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = calender.User.GUID;
                adapter.SelectCommand.Parameters.Add("@SlotId", SqlDbType.UniqueIdentifier).Value = calender.Slot.SlotId;


                DataSet dsMaster = new DataSet();
                adapter.Fill(dsMaster);

                if (dsMaster.Tables[0].Rows.Count > 0)
                    return false;

                if (dsMaster.Tables[1].Rows.Count > 0)
                    return false;

                return true;


            }
            catch (Exception e)
            { return false; }

        }
        public static int DeleteAppointmant(Calendar calendar)
        {
            try
            {
                adapter.SelectCommand.CommandText = "DeleteCalenderAppointmant";
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Clear();
                adapter.SelectCommand.Parameters.Add("@Date", SqlDbType.Date).Value = calendar.Date;
                adapter.SelectCommand.Parameters.Add("@RoomId", SqlDbType.Int).Value = calendar.Room.Id;
                adapter.SelectCommand.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = calendar.User.GUID;
                adapter.SelectCommand.Parameters.Add("@SlotId", SqlDbType.UniqueIdentifier).Value = calendar.Slot.SlotId;
                adapter.SelectCommand.Parameters.Add("@AccountId", SqlDbType.UniqueIdentifier).Value = calendar.Account.GUID;

                OpenConnection();
                int rowindex = command.ExecuteNonQuery();
                CloseConnection();

                return rowindex;

            }
            catch (Exception e)
            { return 0; }
        }
        public static Guid CalendarSaveAccount(string firstname, string secondname, string thirdname, string fourthname, string UNHCR, string Telephone)
        {
            try
            {
                command.CommandText = "CalendarSaveAccount";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();

                command.Parameters.Add("@firstname", SqlDbType.NVarChar).Value = firstname;
                command.Parameters.Add("@secondname", SqlDbType.NVarChar).Value = secondname;
                command.Parameters.Add("@thirdname", SqlDbType.NVarChar).Value = thirdname;
                command.Parameters.Add("@fourthname", SqlDbType.NVarChar).Value = fourthname;
                command.Parameters.Add("@UNHCR", SqlDbType.NVarChar).Value = UNHCR;
                command.Parameters.Add("@Telephone", SqlDbType.NVarChar).Value = Telephone;

                OpenConnection();
                Guid Rowindex = Guid.Parse(command.ExecuteScalar().ToString());
                CloseConnection();
                return Rowindex;
            }
            catch (Exception e) { CloseConnection();  return Guid.Parse(""); }
        }
    }
}
